#!/usr/bin/env bash
cd `dirname $0`
source ../../../config.sh
source ../../basic/app/app_table_data_source.sh
exec_dir teacher_statistics_info

HIVE_DB=app
HIVE_TABLE=teacher_statistics_info
TARGET_TABLE=teacher_statistics_info
MODEL_DB=basic_teacher_info,teacher_growing_assessment_info,course_evaluation_teaching_info,teacher_change_class_info

function create_table(){
 hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :
    hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"
    hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
	semester_year  String comment'学年 YYYY-YYYY',
	 total_num  int comment'师资总数',
	 double_num   int comment'双师素质教师数',
	 core_num   int comment'骨干教师数',
	 leader_num int comment'专业带头人数',
	 assess_avg FLOAT comment'年度考核平均分',
	 teacher_assess_avg  FLOAT comment'师德考核平均分',
	 supervisor_evaluate_avg FLOAT comment'督导评价平均分',
	 student_evaluate_avg FLOAT comment'学生评价平均分',
	 teaching_accident_num int comment'教学事故数',
	 create_time   String  comment'创建时间'
	)COMMENT '师资概况统计'
    LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'"
    fn_log "创建表——师资概况统计：${HIVE_DB}.${HIVE_TABLE}"
}
function import_table() {
create_table
    hive -e "
         INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		 select
			'${SEMESTER_YEAR}' as semester_year,
			a.total_num as total_num,
			a.double_num as double_num,
			a.core_num as core_num,
			a.leader_num as leader_num,
			b.assess_avg as assess_avg,
			b.teacher_assess_avg as teacher_assess_avg,
			c.supervisor_evaluate_avg as supervisor_evaluate_avg,
			c.student_evaluate_avg as student_evaluate_avg,
			d.teaching_accident_num as teaching_accident_num,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			from
			(
			select
			count(a.code) as total_num,
			count(case when is_double_professionally='1' then a.code end)  as double_num,
				count(case when is_core_teacher='1' then a.code end)  as core_num,
				count(case when is_major_leader='1' then a.code end)  as leader_num
			from
			model.basic_teacher_info a
			where a.semester_year='${SEMESTER_YEAR}'
			)a,
			(
			select
				cast(avg(b.assessment_score) as decimal(9,2)) as assess_avg,
				cast(avg(b.character_score) as decimal(9,2)) as teacher_assess_avg
				from
				model.teacher_growing_assessment_info b
				where b.semester_year='${SEMESTER_YEAR}'
			)b,
			(
			select
				cast(avg(IF(c.score is null,0,cast(c.score as int)))/10 as decimal(4,2)) as supervisor_evaluate_avg,
				0 as student_evaluate_avg
				from
				model.course_evaluation_teaching_info c
				where c.semester_year ='${SEMESTER_YEAR}'
			)c,
			(
			select 
			count(d.course_code) as teaching_accident_num
			from
			model.teacher_change_class_info d
			where d.semester_year='${SEMESTER_YEAR}' and d.cousre_type='sg'
			)d
			
		 "
		 fn_log "导入数据 —— 师资概况统计：${HIVE_DB}.${HIVE_TABLE}"
		 export_table
	}
function export_table() {
     clear_mysql_data "TRUNCATE TABLE ${TARGET_TABLE};"
    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N' \
    --columns 'semester_year,total_num,double_num,core_num,leader_num,assess_avg,teacher_assess_avg,supervisor_evaluate_avg,student_evaluate_avg,
	teaching_accident_num,create_time'
    fn_log "导出数据--师资概况统计:${HIVE_DB}.${TARGET_TABLE}"
}
function getYearData(){
    vDate=`date +%Y`
    let vDate+=0;
    years=2
    for((i=1;i<=2;i++));
    do
      let NOW_YEAR=vDate-i+1
      let PRE_YEAR=vDate-i
      SEMESTER_YEAR=${PRE_YEAR}"-"${NOW_YEAR}
      
      echo $SEMESTER_YEAR
	  import_table >> ${RUNLOG} 2>&1
    done
}
RUNLOG=./logs/$0_`date +%Y-%m-%d`.log 2>&1

if [ $# == 1 ]
        then
        getYearData
        data_cleaning
        finish
    else
        getYearData
        finish
    fi